<html>
<head>
<title>firstworks   Routing and Filtering Queries with SQL Relay</title>
<link href="css/styles.css" rel="stylesheet">
</head>
<body>

<span class="heading1">Routing and Filtering Queries with SQL Relay</span><br><br>

<p>SQL Relay's query routing and filtering feature allows you to send one
set of queries to one database, another set of queries to another and filter
out queries entirely so that they aren't sent to any database.</p>

<p>How is this useful?  Here are some examples of what you can do:</p>

<ul>
<li>Send inserts, updates and deletes to a master database and distribute
selects over a set of slaves.</li>
<li>Provide a single point of access to multiple databases, even different
types of databases (eg. MySQL, Oracle, MS SQL Server).</li>
<li>Prevent joins between particular sets of tables.</li>
<li>Filter out queries that lack a where clause.</li>
</ul>

<p>These are not the only things you can do.  Use your imagination.</p>

<span class="heading1">How it Works</span><br><br>

<p>Normally, SQL Relay maintains a set of persistent connections to a database
or database cluster and distributes queries over the connections.  The databases
to connect to are defined in the sqlrelay.conf file.</p>

<p>When used in query routing and filtering mode, SQL Relay is configured to
distribute queries over other instances of SQL Relay running in standard
mode.</p>

<p>For instance, you can set up one instance of SQL Relay to maintain 3
connections to a MySQL database and another instance of SQL Relay to maintain
5 connections to a PostgreSQL database, then set up another instance of
SQL Relay to route queries to the other 2 instances.</p>

<blockquote><img src="router.png"></blockquote>

<p>The router instance decides which queries to send to the other instances by
comparing them to a set of regular expressions.</p>

<span class="heading1">General Configuration</span><br><br>

<p>Each instance of SQL Relay that will be used as a query router must omit
the <b>connection</b> tag and include a single <b>router</b> tag.  The
<b>router</b> tag must contain a set of <b>route</b> and <b>filter</b> tags.
Each <b>route</b> tag defines an instance of SQL Relay to route queries to
and contains a set of <b>query</b> tags defining which queries to route to it.
Each <b>filter</b> tag contains a set of <b>query</b> tags defining which
queries to filter out.</p>

<p>When deciding where to route a query (or whether to filter it out), SQL
Relay runs through the <b>query</b> tags, in the order that they appear in the
sqlrelay.conf file, and compares the query to the regular expression defined in
the tag.  If the query matches a regular expression defined in a <b>query</b>
tag inside of a <b>route</b> tag, it will be routed to the instance of SQL Relay
defined in the <b>route</b> tag.  If the query matches a regular expression
defined in a <b>query</b> tag inside of a <b>filter</b> tag, it will be filtered
out.</p>

<p>See the examples below.  Note that the regular expressions in the
examples below aren't perfect.  They are not case insensitive, for example, nor
do they necessarily cover all possible cases.  In some cases, better, or more
regular expressions could be written.  In other cases, developers may need to
structure their queries so that they are guaranteed to match.</p>

<span class="heading1">Sending Inserts, Updates and Deletes to a Master
Database and Distributing Selects Over a Set of Slaves</span>

<p>Here's an example configuration for sending inserts, updates and delete's to
a master database and distributing selects over a set of slaves.</p>

<p>In this example, 3 instances of SQL Relay are defined: one to maintain
connections to the "master" database, another to maintain connections to a
set of "slave" databases, and another to route quries over the "master" and
"slave" instances.  Clients would connect to the "router" instance (on port 9001
or to the /tmp/router.socket unix socket).  In this example, all 3 instances
of SQL Relay run on the same machine, but in reality, all 3 could run on
separate machines.</p>

<blockquote>
<pre>
<font color="#0000ff">&lt;?</font><font color="#2e8b57"><b>xml</b></font><font color="#2e8b57"><b> </b></font><font color="#2e8b57"><b>version</b></font>=<font color="#ff00ff">&quot;1.0&quot;</font><font color="#0000ff">?&gt;</font>
<font color="#008b8b">&lt;!</font><font color="#a52a2a"><b>DOCTYPE</b></font> instances <font color="#a52a2a"><b>SYSTEM</b></font> <font color="#ff00ff">&quot;sqlrelay.dtd&quot;</font><font color="#008b8b">&gt;</font>

<font color="#008b8b">&lt;</font><font color="#008b8b">instances</font><font color="#008b8b">&gt;</font>

        <font color="#0000ff">&lt;!</font><font color="#0000ff">-- This instance maintains connections to the &quot;master&quot; MySQL database</font>
<font color="#0000ff">                on the masterdb machine.  This instance only listens on the</font>
<font color="#0000ff">                unix socket /tmp/master.socket and thus cannot be connected to</font>
<font color="#0000ff">                by clients from another machine. --</font><font color="#0000ff">&gt;</font>
        <font color="#008b8b">&lt;</font><font color="#008b8b">instance</font><font color="#008b8b"> </font><font color="#2e8b57"><b>id</b></font>=<font color="#ff00ff">&quot;master&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>port</b></font>=<font color="#ff00ff">&quot;&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>socket</b></font>=<font color="#ff00ff">&quot;/tmp/master.socket&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>dbase</b></font>=<font color="#ff00ff">&quot;mysql&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>connections</b></font>=<font color="#ff00ff">&quot;3&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>maxconnections</b></font>=<font color="#ff00ff">&quot;15&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>maxqueuelength</b></font>=<font color="#ff00ff">&quot;5&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>growby</b></font>=<font color="#ff00ff">&quot;1&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>ttl</b></font>=<font color="#ff00ff">&quot;60&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>endofsession</b></font>=<font color="#ff00ff">&quot;commit&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>sessiontimeout</b></font>=<font color="#ff00ff">&quot;600&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>runasuser</b></font>=<font color="#ff00ff">&quot;nobody&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>runasgroup</b></font>=<font color="#ff00ff">&quot;nobody&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>cursors</b></font>=<font color="#ff00ff">&quot;5&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>authtier</b></font>=<font color="#ff00ff">&quot;listener&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>handoff</b></font>=<font color="#ff00ff">&quot;pass&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>deniedips</b></font>=<font color="#ff00ff">&quot;&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>allowedips</b></font>=<font color="#ff00ff">&quot;&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>debug</b></font>=<font color="#ff00ff">&quot;none&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>maxquerysize</b></font>=<font color="#ff00ff">&quot;65536&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>maxstringbindvaluelength</b></font>=<font color="#ff00ff">&quot;4000&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>maxlobbindvaluelength</b></font>=<font color="#ff00ff">&quot;71680&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>idleclienttimeout</b></font>=<font color="#ff00ff">&quot;-1&quot;</font><font color="#008b8b">&gt;</font>
                <font color="#008b8b">&lt;</font><font color="#008b8b">users</font><font color="#008b8b">&gt;</font>
                        <font color="#008b8b">&lt;</font><font color="#008b8b">user</font><font color="#008b8b"> </font><font color="#2e8b57"><b>user</b></font>=<font color="#ff00ff">&quot;masteruser&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>password</b></font>=<font color="#ff00ff">&quot;masterpassword&quot;</font><font color="#008b8b">/&gt;</font>
                <font color="#008b8b">&lt;/users&gt;</font>
                <font color="#008b8b">&lt;</font><font color="#008b8b">connections</font><font color="#008b8b">&gt;</font>
                        <font color="#008b8b">&lt;</font><font color="#008b8b">connection</font><font color="#008b8b"> </font><font color="#2e8b57"><b>connectionid</b></font>=<font color="#ff00ff">&quot;db&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>string</b></font>=<font color="#ff00ff">&quot;user=masteruser;password=masterpassword;host=masterdb;db=master;&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>metric</b></font>=<font color="#ff00ff">&quot;1&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>behindloadbalancer</b></font>=<font color="#ff00ff">&quot;no&quot;</font><font color="#008b8b">/&gt;</font>
                <font color="#008b8b">&lt;/connections&gt;</font>
        <font color="#008b8b">&lt;/instance&gt;</font>


        <font color="#0000ff">&lt;!</font><font color="#0000ff">-- This instance maintains connections to 4 &quot;slave&quot; MySQL databases</font>
<font color="#0000ff">                on 4 slave machines.  This instance only listens on the unix</font>
<font color="#0000ff">                socket /tmp/slave.socket and thus cannot be connected to by</font>
<font color="#0000ff">                clients from another machine. --</font><font color="#0000ff">&gt;</font>
        <font color="#008b8b">&lt;</font><font color="#008b8b">instance</font><font color="#008b8b"> </font><font color="#2e8b57"><b>id</b></font>=<font color="#ff00ff">&quot;slave&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>port</b></font>=<font color="#ff00ff">&quot;&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>socket</b></font>=<font color="#ff00ff">&quot;/tmp/slave.socket&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>dbase</b></font>=<font color="#ff00ff">&quot;mysql&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>connections</b></font>=<font color="#ff00ff">&quot;3&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>maxconnections</b></font>=<font color="#ff00ff">&quot;15&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>maxqueuelength</b></font>=<font color="#ff00ff">&quot;5&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>growby</b></font>=<font color="#ff00ff">&quot;1&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>ttl</b></font>=<font color="#ff00ff">&quot;60&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>endofsession</b></font>=<font color="#ff00ff">&quot;commit&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>sessiontimeout</b></font>=<font color="#ff00ff">&quot;600&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>runasuser</b></font>=<font color="#ff00ff">&quot;nobody&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>runasgroup</b></font>=<font color="#ff00ff">&quot;nobody&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>cursors</b></font>=<font color="#ff00ff">&quot;5&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>authtier</b></font>=<font color="#ff00ff">&quot;listener&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>handoff</b></font>=<font color="#ff00ff">&quot;pass&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>deniedips</b></font>=<font color="#ff00ff">&quot;&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>allowedips</b></font>=<font color="#ff00ff">&quot;&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>debug</b></font>=<font color="#ff00ff">&quot;none&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>maxquerysize</b></font>=<font color="#ff00ff">&quot;65536&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>maxstringbindvaluelength</b></font>=<font color="#ff00ff">&quot;4000&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>maxlobbindvaluelength</b></font>=<font color="#ff00ff">&quot;71680&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>idleclienttimeout</b></font>=<font color="#ff00ff">&quot;-1&quot;</font><font color="#008b8b">&gt;</font>
                <font color="#008b8b">&lt;</font><font color="#008b8b">users</font><font color="#008b8b">&gt;</font>
                        <font color="#008b8b">&lt;</font><font color="#008b8b">user</font><font color="#008b8b"> </font><font color="#2e8b57"><b>user</b></font>=<font color="#ff00ff">&quot;slaveuser&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>password</b></font>=<font color="#ff00ff">&quot;slavepassword&quot;</font><font color="#008b8b">/&gt;</font>
                <font color="#008b8b">&lt;/users&gt;</font>
                <font color="#008b8b">&lt;</font><font color="#008b8b">connections</font><font color="#008b8b">&gt;</font>
                        <font color="#008b8b">&lt;</font><font color="#008b8b">connection</font><font color="#008b8b"> </font><font color="#2e8b57"><b>connectionid</b></font>=<font color="#ff00ff">&quot;db1&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>string</b></font>=<font color="#ff00ff">&quot;user=slaveuser;password=slavepassword;host=slavedb1;db=slave;&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>metric</b></font>=<font color="#ff00ff">&quot;1&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>behindloadbalancer</b></font>=<font color="#ff00ff">&quot;no&quot;</font><font color="#008b8b">/&gt;</font>
                        <font color="#008b8b">&lt;</font><font color="#008b8b">connection</font><font color="#008b8b"> </font><font color="#2e8b57"><b>connectionid</b></font>=<font color="#ff00ff">&quot;db2&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>string</b></font>=<font color="#ff00ff">&quot;user=slaveuser;password=slavepassword;host=slavedb2;db=slave;&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>metric</b></font>=<font color="#ff00ff">&quot;1&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>behindloadbalancer</b></font>=<font color="#ff00ff">&quot;no&quot;</font><font color="#008b8b">/&gt;</font>
                        <font color="#008b8b">&lt;</font><font color="#008b8b">connection</font><font color="#008b8b"> </font><font color="#2e8b57"><b>connectionid</b></font>=<font color="#ff00ff">&quot;db3&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>string</b></font>=<font color="#ff00ff">&quot;user=slaveuser;password=slavepassword;host=slavedb3;db=slave;&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>metric</b></font>=<font color="#ff00ff">&quot;1&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>behindloadbalancer</b></font>=<font color="#ff00ff">&quot;no&quot;</font><font color="#008b8b">/&gt;</font>
                        <font color="#008b8b">&lt;</font><font color="#008b8b">connection</font><font color="#008b8b"> </font><font color="#2e8b57"><b>connectionid</b></font>=<font color="#ff00ff">&quot;db4&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>string</b></font>=<font color="#ff00ff">&quot;user=slaveuser;password=slavepassword;host=slavedb3;db=slave;&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>metric</b></font>=<font color="#ff00ff">&quot;1&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>behindloadbalancer</b></font>=<font color="#ff00ff">&quot;no&quot;</font><font color="#008b8b">/&gt;</font>
                <font color="#008b8b">&lt;/connections&gt;</font>
        <font color="#008b8b">&lt;/instance&gt;</font>


        <font color="#0000ff">&lt;!</font><font color="#0000ff">-- This instance sends DML (insert,update,delete) and</font>
<font color="#0000ff">                DDL (create/delete) queries to the &quot;master&quot; SQL Relay instance</font>
<font color="#0000ff">                which, in turn, sends them to the &quot;master&quot; database.</font>
<font color="#0000ff">                This instance sends any other queries to the &quot;slave&quot; SQL Relay</font>
<font color="#0000ff">                instance which, in turn, distributes them over the &quot;slave&quot;</font>
<font color="#0000ff">                databases. --</font><font color="#0000ff">&gt;</font>
        <font color="#008b8b">&lt;</font><font color="#008b8b">instance</font><font color="#008b8b"> </font><font color="#2e8b57"><b>id</b></font>=<font color="#ff00ff">&quot;router&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>port</b></font>=<font color="#ff00ff">&quot;9000&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>socket</b></font>=<font color="#ff00ff">&quot;/tmp/router.socket&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>dbase</b></font>=<font color="#ff00ff">&quot;router&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>connections</b></font>=<font color="#ff00ff">&quot;3&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>maxconnections</b></font>=<font color="#ff00ff">&quot;15&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>maxqueuelength</b></font>=<font color="#ff00ff">&quot;5&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>growby</b></font>=<font color="#ff00ff">&quot;1&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>ttl</b></font>=<font color="#ff00ff">&quot;60&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>endofsession</b></font>=<font color="#ff00ff">&quot;commit&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>sessiontimeout</b></font>=<font color="#ff00ff">&quot;600&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>runasuser</b></font>=<font color="#ff00ff">&quot;nobody&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>runasgroup</b></font>=<font color="#ff00ff">&quot;nobody&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>cursors</b></font>=<font color="#ff00ff">&quot;5&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>authtier</b></font>=<font color="#ff00ff">&quot;listener&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>handoff</b></font>=<font color="#ff00ff">&quot;pass&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>deniedips</b></font>=<font color="#ff00ff">&quot;&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>allowedips</b></font>=<font color="#ff00ff">&quot;&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>debug</b></font>=<font color="#ff00ff">&quot;none&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>maxquerysize</b></font>=<font color="#ff00ff">&quot;65536&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>maxstringbindvaluelength</b></font>=<font color="#ff00ff">&quot;4000&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>maxlobbindvaluelength</b></font>=<font color="#ff00ff">&quot;71680&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>idleclienttimeout</b></font>=<font color="#ff00ff">&quot;-1&quot;</font><font color="#008b8b">&gt;</font>
                <font color="#008b8b">&lt;</font><font color="#008b8b">users</font><font color="#008b8b">&gt;</font>
                        <font color="#008b8b">&lt;</font><font color="#008b8b">user</font><font color="#008b8b"> </font><font color="#2e8b57"><b>user</b></font>=<font color="#ff00ff">&quot;routeruser&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>password</b></font>=<font color="#ff00ff">&quot;routerpassword&quot;</font><font color="#008b8b">/&gt;</font>
                <font color="#008b8b">&lt;/users&gt;</font>
                <font color="#008b8b">&lt;</font><font color="#008b8b">router</font><font color="#008b8b">&gt;</font>
                        <font color="#0000ff">&lt;!</font><font color="#0000ff">-- send all DML/DDL queries to &quot;master&quot;  --</font><font color="#0000ff">&gt;</font>
                        <font color="#008b8b">&lt;</font><font color="#008b8b">route</font><font color="#008b8b"> </font><font color="#2e8b57"><b>host</b></font>=<font color="#ff00ff">&quot;&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>port</b></font>=<font color="#ff00ff">&quot;&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>socket</b></font>=<font color="#ff00ff">&quot;/tmp/master.socket&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>user</b></font>=<font color="#ff00ff">&quot;masteruser&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>password</b></font>=<font color="#ff00ff">&quot;masterpassword&quot;</font><font color="#008b8b">&gt;</font>
                                <font color="#008b8b">&lt;</font><font color="#008b8b">query</font><font color="#008b8b"> </font><font color="#2e8b57"><b>pattern</b></font>=<font color="#ff00ff">&quot;^\s*select\s+.*\s+from\s+&quot;</font><font color="#008b8b">/&gt;</font>
                                <font color="#008b8b">&lt;</font><font color="#008b8b">query</font><font color="#008b8b"> </font><font color="#2e8b57"><b>pattern</b></font>=<font color="#ff00ff">&quot;^\s*insert\s+into\s+&quot;</font><font color="#008b8b">/&gt;</font>
                                <font color="#008b8b">&lt;</font><font color="#008b8b">query</font><font color="#008b8b"> </font><font color="#2e8b57"><b>pattern</b></font>=<font color="#ff00ff">&quot;^\s*update\s+&quot;</font><font color="#008b8b">/&gt;</font>
                                <font color="#008b8b">&lt;</font><font color="#008b8b">query</font><font color="#008b8b"> </font><font color="#2e8b57"><b>pattern</b></font>=<font color="#ff00ff">&quot;^\s*delete\s+from\s+&quot;</font><font color="#008b8b">/&gt;</font>
                                <font color="#008b8b">&lt;</font><font color="#008b8b">query</font><font color="#008b8b"> </font><font color="#2e8b57"><b>pattern</b></font>=<font color="#ff00ff">&quot;^\s*drop\s+table\s+&quot;</font><font color="#008b8b">/&gt;</font>
                                <font color="#008b8b">&lt;</font><font color="#008b8b">query</font><font color="#008b8b"> </font><font color="#2e8b57"><b>pattern</b></font>=<font color="#ff00ff">&quot;^\s*create\s+table\s+&quot;</font><font color="#008b8b">/&gt;</font>
                        <font color="#008b8b">&lt;/route&gt;</font>
                        <font color="#0000ff">&lt;!</font><font color="#0000ff">-- send all other queries to &quot;slave&quot; --</font><font color="#0000ff">&gt;</font>
                        <font color="#008b8b">&lt;</font><font color="#008b8b">route</font><font color="#008b8b"> </font><font color="#2e8b57"><b>host</b></font>=<font color="#ff00ff">&quot;&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>port</b></font>=<font color="#ff00ff">&quot;&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>socket</b></font>=<font color="#ff00ff">&quot;/tmp/slave.socket&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>user</b></font>=<font color="#ff00ff">&quot;slaveuser&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>password</b></font>=<font color="#ff00ff">&quot;slavepassword&quot;</font><font color="#008b8b">&gt;</font>
                                <font color="#008b8b">&lt;</font><font color="#008b8b">query</font><font color="#008b8b"> </font><font color="#2e8b57"><b>pattern</b></font>=<font color="#ff00ff">&quot;.*&quot;</font><font color="#008b8b">/&gt;</font>
                        <font color="#008b8b">&lt;/route&gt;</font>
                <font color="#008b8b">&lt;/router&gt;</font>
        <font color="#008b8b">&lt;/instance&gt;</font>

<font color="#008b8b">&lt;/instances&gt;</font>
</pre>
</blockquote>

<span class="heading1">Provide a Single Point of Access to Multiple Databases</span>

<p>Here's an example configuration providing a single point of access to
multiple databases.</p>

<p>In this example, we provide a single point of access to the MySQL database
"mysqldb" and the PostgreSQL database "postgresqldb".  Since both databases
support the "database.tablename" syntax, we can use that format when specifying
tables and send all queries containing "mysqldb." to the MySQL database and
all queries containing "postgresqldb." to the PostgreSQL database.</p>

<blockquote>
<pre>
<font color="#0000ff">&lt;?</font><font color="#2e8b57"><b>xml</b></font><font color="#2e8b57"><b> </b></font><font color="#2e8b57"><b>version</b></font>=<font color="#ff00ff">&quot;1.0&quot;</font><font color="#0000ff">?&gt;</font>
<font color="#008b8b">&lt;!</font><font color="#a52a2a"><b>DOCTYPE</b></font> instances <font color="#a52a2a"><b>SYSTEM</b></font> <font color="#ff00ff">&quot;sqlrelay.dtd&quot;</font><font color="#008b8b">&gt;</font>

<font color="#008b8b">&lt;</font><font color="#008b8b">instances</font><font color="#008b8b">&gt;</font>

        <font color="#0000ff">&lt;!</font><font color="#0000ff">-- This instance maintains connections to a MySQL database --</font><font color="#0000ff">&gt;</font>
        <font color="#008b8b">&lt;</font><font color="#008b8b">instance</font><font color="#008b8b"> </font><font color="#2e8b57"><b>id</b></font>=<font color="#ff00ff">&quot;mysqldb&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>port</b></font>=<font color="#ff00ff">&quot;&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>socket</b></font>=<font color="#ff00ff">&quot;/tmp/mysqldb.socket&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>dbase</b></font>=<font color="#ff00ff">&quot;mysql&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>connections</b></font>=<font color="#ff00ff">&quot;3&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>maxconnections</b></font>=<font color="#ff00ff">&quot;15&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>maxqueuelength</b></font>=<font color="#ff00ff">&quot;5&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>growby</b></font>=<font color="#ff00ff">&quot;1&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>ttl</b></font>=<font color="#ff00ff">&quot;60&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>endofsession</b></font>=<font color="#ff00ff">&quot;commit&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>sessiontimeout</b></font>=<font color="#ff00ff">&quot;600&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>runasuser</b></font>=<font color="#ff00ff">&quot;nobody&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>runasgroup</b></font>=<font color="#ff00ff">&quot;nobody&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>cursors</b></font>=<font color="#ff00ff">&quot;5&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>authtier</b></font>=<font color="#ff00ff">&quot;listener&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>handoff</b></font>=<font color="#ff00ff">&quot;pass&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>deniedips</b></font>=<font color="#ff00ff">&quot;&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>allowedips</b></font>=<font color="#ff00ff">&quot;&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>debug</b></font>=<font color="#ff00ff">&quot;none&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>maxquerysize</b></font>=<font color="#ff00ff">&quot;65536&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>maxstringbindvaluelength</b></font>=<font color="#ff00ff">&quot;4000&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>maxlobbindvaluelength</b></font>=<font color="#ff00ff">&quot;71680&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>idleclienttimeout</b></font>=<font color="#ff00ff">&quot;-1&quot;</font><font color="#008b8b">&gt;</font>
                <font color="#008b8b">&lt;</font><font color="#008b8b">users</font><font color="#008b8b">&gt;</font>
                        <font color="#008b8b">&lt;</font><font color="#008b8b">user</font><font color="#008b8b"> </font><font color="#2e8b57"><b>user</b></font>=<font color="#ff00ff">&quot;mysqldbuser&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>password</b></font>=<font color="#ff00ff">&quot;mysqldbpassword&quot;</font><font color="#008b8b">/&gt;</font>
                <font color="#008b8b">&lt;/users&gt;</font>
                <font color="#008b8b">&lt;</font><font color="#008b8b">connections</font><font color="#008b8b">&gt;</font>
                        <font color="#008b8b">&lt;</font><font color="#008b8b">connection</font><font color="#008b8b"> </font><font color="#2e8b57"><b>connectionid</b></font>=<font color="#ff00ff">&quot;db&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>string</b></font>=<font color="#ff00ff">&quot;user=mysqldbuser;password=mysqldbpassword;host=mysqldb;db=mysqldb;&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>metric</b></font>=<font color="#ff00ff">&quot;1&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>behindloadbalancer</b></font>=<font color="#ff00ff">&quot;no&quot;</font><font color="#008b8b">/&gt;</font>
                <font color="#008b8b">&lt;/connections&gt;</font>
        <font color="#008b8b">&lt;/instance&gt;</font>


        <font color="#0000ff">&lt;!</font><font color="#0000ff">-- This instance maintains connections to a PostgreSQL database --</font><font color="#0000ff">&gt;</font>
        <font color="#008b8b">&lt;</font><font color="#008b8b">instance</font><font color="#008b8b"> </font><font color="#2e8b57"><b>id</b></font>=<font color="#ff00ff">&quot;postgresqldb&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>port</b></font>=<font color="#ff00ff">&quot;&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>socket</b></font>=<font color="#ff00ff">&quot;/tmp/postgresqldb.socket&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>dbase</b></font>=<font color="#ff00ff">&quot;postgresql&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>connections</b></font>=<font color="#ff00ff">&quot;3&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>maxconnections</b></font>=<font color="#ff00ff">&quot;15&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>maxqueuelength</b></font>=<font color="#ff00ff">&quot;5&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>growby</b></font>=<font color="#ff00ff">&quot;1&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>ttl</b></font>=<font color="#ff00ff">&quot;60&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>endofsession</b></font>=<font color="#ff00ff">&quot;commit&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>sessiontimeout</b></font>=<font color="#ff00ff">&quot;600&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>runasuser</b></font>=<font color="#ff00ff">&quot;nobody&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>runasgroup</b></font>=<font color="#ff00ff">&quot;nobody&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>cursors</b></font>=<font color="#ff00ff">&quot;5&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>authtier</b></font>=<font color="#ff00ff">&quot;listener&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>handoff</b></font>=<font color="#ff00ff">&quot;pass&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>deniedips</b></font>=<font color="#ff00ff">&quot;&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>allowedips</b></font>=<font color="#ff00ff">&quot;&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>debug</b></font>=<font color="#ff00ff">&quot;none&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>maxquerysize</b></font>=<font color="#ff00ff">&quot;65536&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>maxstringbindvaluelength</b></font>=<font color="#ff00ff">&quot;4000&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>maxlobbindvaluelength</b></font>=<font color="#ff00ff">&quot;71680&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>idleclienttimeout</b></font>=<font color="#ff00ff">&quot;-1&quot;</font><font color="#008b8b">&gt;</font>
                <font color="#008b8b">&lt;</font><font color="#008b8b">users</font><font color="#008b8b">&gt;</font>
                        <font color="#008b8b">&lt;</font><font color="#008b8b">user</font><font color="#008b8b"> </font><font color="#2e8b57"><b>user</b></font>=<font color="#ff00ff">&quot;postgresqldbuser&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>password</b></font>=<font color="#ff00ff">&quot;postgresqldbpassword&quot;</font><font color="#008b8b">/&gt;</font>
                <font color="#008b8b">&lt;/users&gt;</font>
                <font color="#008b8b">&lt;</font><font color="#008b8b">connections</font><font color="#008b8b">&gt;</font>
                        <font color="#008b8b">&lt;</font><font color="#008b8b">connection</font><font color="#008b8b"> </font><font color="#2e8b57"><b>connectionid</b></font>=<font color="#ff00ff">&quot;db&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>string</b></font>=<font color="#ff00ff">&quot;user=postgresqldbuser;password=postgresqldbpassword;host=postgresqldb;db=postgresqldb;&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>metric</b></font>=<font color="#ff00ff">&quot;1&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>behindloadbalancer</b></font>=<font color="#ff00ff">&quot;no&quot;</font><font color="#008b8b">/&gt;</font>
                <font color="#008b8b">&lt;/connections&gt;</font>
        <font color="#008b8b">&lt;/instance&gt;</font>


        <font color="#0000ff">&lt;!</font><font color="#0000ff">-- This instance sends queries containing &quot;mysqldb.&quot; to the mysql</font>
<font color="#0000ff">                database and &quot;postgresqldb.&quot; to the postgresql database --</font><font color="#0000ff">&gt;</font>
        <font color="#008b8b">&lt;</font><font color="#008b8b">instance</font><font color="#008b8b"> </font><font color="#2e8b57"><b>id</b></font>=<font color="#ff00ff">&quot;router&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>port</b></font>=<font color="#ff00ff">&quot;9000&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>socket</b></font>=<font color="#ff00ff">&quot;/tmp/router.socket&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>dbase</b></font>=<font color="#ff00ff">&quot;router&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>connections</b></font>=<font color="#ff00ff">&quot;3&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>maxconnections</b></font>=<font color="#ff00ff">&quot;15&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>maxqueuelength</b></font>=<font color="#ff00ff">&quot;5&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>growby</b></font>=<font color="#ff00ff">&quot;1&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>ttl</b></font>=<font color="#ff00ff">&quot;60&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>endofsession</b></font>=<font color="#ff00ff">&quot;commit&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>sessiontimeout</b></font>=<font color="#ff00ff">&quot;600&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>runasuser</b></font>=<font color="#ff00ff">&quot;nobody&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>runasgroup</b></font>=<font color="#ff00ff">&quot;nobody&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>cursors</b></font>=<font color="#ff00ff">&quot;5&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>authtier</b></font>=<font color="#ff00ff">&quot;listener&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>handoff</b></font>=<font color="#ff00ff">&quot;pass&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>deniedips</b></font>=<font color="#ff00ff">&quot;&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>allowedips</b></font>=<font color="#ff00ff">&quot;&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>debug</b></font>=<font color="#ff00ff">&quot;none&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>maxquerysize</b></font>=<font color="#ff00ff">&quot;65536&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>maxstringbindvaluelength</b></font>=<font color="#ff00ff">&quot;4000&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>maxlobbindvaluelength</b></font>=<font color="#ff00ff">&quot;71680&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>idleclienttimeout</b></font>=<font color="#ff00ff">&quot;-1&quot;</font><font color="#008b8b">&gt;</font>
                <font color="#008b8b">&lt;</font><font color="#008b8b">users</font><font color="#008b8b">&gt;</font>
                        <font color="#008b8b">&lt;</font><font color="#008b8b">user</font><font color="#008b8b"> </font><font color="#2e8b57"><b>user</b></font>=<font color="#ff00ff">&quot;routeruser&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>password</b></font>=<font color="#ff00ff">&quot;routerpassword&quot;</font><font color="#008b8b">/&gt;</font>
                <font color="#008b8b">&lt;/users&gt;</font>
                <font color="#008b8b">&lt;</font><font color="#008b8b">router</font><font color="#008b8b">&gt;</font>
                        <font color="#0000ff">&lt;!</font><font color="#0000ff">-- send all mysqldb queries to &quot;mysqldb&quot; --</font><font color="#0000ff">&gt;</font>
                        <font color="#008b8b">&lt;</font><font color="#008b8b">route</font><font color="#008b8b"> </font><font color="#2e8b57"><b>host</b></font>=<font color="#ff00ff">&quot;&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>port</b></font>=<font color="#ff00ff">&quot;&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>socket</b></font>=<font color="#ff00ff">&quot;/tmp/mysqldb.socket&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>user</b></font>=<font color="#ff00ff">&quot;mysqldbuser&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>password</b></font>=<font color="#ff00ff">&quot;mysqldbpassword&quot;</font><font color="#008b8b">&gt;</font>
                                <font color="#008b8b">&lt;</font><font color="#008b8b">query</font><font color="#008b8b"> </font><font color="#2e8b57"><b>pattern</b></font>=<font color="#ff00ff">&quot;+\smysqldb\.&quot;</font><font color="#008b8b">/&gt;</font>
                        <font color="#008b8b">&lt;/route&gt;</font>
                        <font color="#0000ff">&lt;!</font><font color="#0000ff">-- send all postgresqldb queries to &quot;postgresqldb&quot; --</font><font color="#0000ff">&gt;</font>
                        <font color="#008b8b">&lt;</font><font color="#008b8b">route</font><font color="#008b8b"> </font><font color="#2e8b57"><b>host</b></font>=<font color="#ff00ff">&quot;&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>port</b></font>=<font color="#ff00ff">&quot;&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>socket</b></font>=<font color="#ff00ff">&quot;/tmp/postgresqldb.socket&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>user</b></font>=<font color="#ff00ff">&quot;postgresqldbuser&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>password</b></font>=<font color="#ff00ff">&quot;postgresqldbpassword&quot;</font><font color="#008b8b">&gt;</font>
                                <font color="#008b8b">&lt;</font><font color="#008b8b">query</font><font color="#008b8b"> </font><font color="#2e8b57"><b>pattern</b></font>=<font color="#ff00ff">&quot;+\spostgresqldb\.&quot;</font><font color="#008b8b">/&gt;</font>
                        <font color="#008b8b">&lt;/route&gt;</font>
                <font color="#008b8b">&lt;/router&gt;</font>
        <font color="#008b8b">&lt;/instance&gt;</font>

<font color="#008b8b">&lt;/instances&gt;</font>
</pre>
</blockquote>

<span class="heading1">Prevent Joins Between Particular Sets of Tables</span>

<p>Here's an example configuration which prevent joins between particular sets
of tables.</span>

<p>In this example, any select containing both table1 and table3 will be
filtered out.</p>

<blockquote>
<pre>
<font color="#0000ff">&lt;?</font><font color="#2e8b57"><b>xml</b></font><font color="#2e8b57"><b> </b></font><font color="#2e8b57"><b>version</b></font>=<font color="#ff00ff">&quot;1.0&quot;</font><font color="#0000ff">?&gt;</font>
<font color="#008b8b">&lt;!</font><font color="#a52a2a"><b>DOCTYPE</b></font> instances <font color="#a52a2a"><b>SYSTEM</b></font> <font color="#ff00ff">&quot;sqlrelay.dtd&quot;</font><font color="#008b8b">&gt;</font>

<font color="#008b8b">&lt;</font><font color="#008b8b">instances</font><font color="#008b8b">&gt;</font>

        <font color="#0000ff">&lt;!</font><font color="#0000ff">-- This instance maintains connections to a MySQL database --</font><font color="#0000ff">&gt;</font>
        <font color="#008b8b">&lt;</font><font color="#008b8b">instance</font><font color="#008b8b"> </font><font color="#2e8b57"><b>id</b></font>=<font color="#ff00ff">&quot;mysqldb&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>port</b></font>=<font color="#ff00ff">&quot;&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>socket</b></font>=<font color="#ff00ff">&quot;/tmp/mysqldb.socket&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>dbase</b></font>=<font color="#ff00ff">&quot;mysql&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>connections</b></font>=<font color="#ff00ff">&quot;3&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>maxconnections</b></font>=<font color="#ff00ff">&quot;15&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>maxqueuelength</b></font>=<font color="#ff00ff">&quot;5&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>growby</b></font>=<font color="#ff00ff">&quot;1&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>ttl</b></font>=<font color="#ff00ff">&quot;60&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>endofsession</b></font>=<font color="#ff00ff">&quot;commit&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>sessiontimeout</b></font>=<font color="#ff00ff">&quot;600&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>runasuser</b></font>=<font color="#ff00ff">&quot;nobody&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>runasgroup</b></font>=<font color="#ff00ff">&quot;nobody&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>cursors</b></font>=<font color="#ff00ff">&quot;5&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>authtier</b></font>=<font color="#ff00ff">&quot;listener&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>handoff</b></font>=<font color="#ff00ff">&quot;pass&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>deniedips</b></font>=<font color="#ff00ff">&quot;&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>allowedips</b></font>=<font color="#ff00ff">&quot;&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>debug</b></font>=<font color="#ff00ff">&quot;none&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>maxquerysize</b></font>=<font color="#ff00ff">&quot;65536&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>maxstringbindvaluelength</b></font>=<font color="#ff00ff">&quot;4000&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>maxlobbindvaluelength</b></font>=<font color="#ff00ff">&quot;71680&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>idleclienttimeout</b></font>=<font color="#ff00ff">&quot;-1&quot;</font><font color="#008b8b">&gt;</font>
                <font color="#008b8b">&lt;</font><font color="#008b8b">users</font><font color="#008b8b">&gt;</font>
                        <font color="#008b8b">&lt;</font><font color="#008b8b">user</font><font color="#008b8b"> </font><font color="#2e8b57"><b>user</b></font>=<font color="#ff00ff">&quot;mysqldbuser&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>password</b></font>=<font color="#ff00ff">&quot;mysqldbpassword&quot;</font><font color="#008b8b">/&gt;</font>
                <font color="#008b8b">&lt;/users&gt;</font>
                <font color="#008b8b">&lt;</font><font color="#008b8b">connections</font><font color="#008b8b">&gt;</font>
                        <font color="#008b8b">&lt;</font><font color="#008b8b">connection</font><font color="#008b8b"> </font><font color="#2e8b57"><b>connectionid</b></font>=<font color="#ff00ff">&quot;db&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>string</b></font>=<font color="#ff00ff">&quot;user=mysqldbuser;password=mysqldbpassword;host=mysqldb;db=mysqldb;&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>metric</b></font>=<font color="#ff00ff">&quot;1&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>behindloadbalancer</b></font>=<font color="#ff00ff">&quot;no&quot;</font><font color="#008b8b">/&gt;</font>
                <font color="#008b8b">&lt;/connections&gt;</font>
        <font color="#008b8b">&lt;/instance&gt;</font>


        <font color="#0000ff">&lt;!</font><font color="#0000ff">-- This instance sends queries to the</font>
<font color="#0000ff">                mysqldb instance after filtering them --</font><font color="#0000ff">&gt;</font>
        <font color="#008b8b">&lt;</font><font color="#008b8b">instance</font><font color="#008b8b"> </font><font color="#2e8b57"><b>id</b></font>=<font color="#ff00ff">&quot;router&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>port</b></font>=<font color="#ff00ff">&quot;9000&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>socket</b></font>=<font color="#ff00ff">&quot;/tmp/router.socket&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>dbase</b></font>=<font color="#ff00ff">&quot;router&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>connections</b></font>=<font color="#ff00ff">&quot;3&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>maxconnections</b></font>=<font color="#ff00ff">&quot;15&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>maxqueuelength</b></font>=<font color="#ff00ff">&quot;5&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>growby</b></font>=<font color="#ff00ff">&quot;1&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>ttl</b></font>=<font color="#ff00ff">&quot;60&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>endofsession</b></font>=<font color="#ff00ff">&quot;commit&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>sessiontimeout</b></font>=<font color="#ff00ff">&quot;600&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>runasuser</b></font>=<font color="#ff00ff">&quot;nobody&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>runasgroup</b></font>=<font color="#ff00ff">&quot;nobody&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>cursors</b></font>=<font color="#ff00ff">&quot;5&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>authtier</b></font>=<font color="#ff00ff">&quot;listener&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>handoff</b></font>=<font color="#ff00ff">&quot;pass&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>deniedips</b></font>=<font color="#ff00ff">&quot;&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>allowedips</b></font>=<font color="#ff00ff">&quot;&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>debug</b></font>=<font color="#ff00ff">&quot;none&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>maxquerysize</b></font>=<font color="#ff00ff">&quot;65536&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>maxstringbindvaluelength</b></font>=<font color="#ff00ff">&quot;4000&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>maxlobbindvaluelength</b></font>=<font color="#ff00ff">&quot;71680&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>idleclienttimeout</b></font>=<font color="#ff00ff">&quot;-1&quot;</font><font color="#008b8b">&gt;</font>
                <font color="#008b8b">&lt;</font><font color="#008b8b">users</font><font color="#008b8b">&gt;</font>
                        <font color="#008b8b">&lt;</font><font color="#008b8b">user</font><font color="#008b8b"> </font><font color="#2e8b57"><b>user</b></font>=<font color="#ff00ff">&quot;routeruser&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>password</b></font>=<font color="#ff00ff">&quot;routerpassword&quot;</font><font color="#008b8b">/&gt;</font>
                <font color="#008b8b">&lt;/users&gt;</font>
                <font color="#008b8b">&lt;</font><font color="#008b8b">router</font><font color="#008b8b">&gt;</font>
                        <font color="#0000ff">&lt;!</font><font color="#0000ff">-- filter out selects containing table1 and table3 --</font><font color="#0000ff">&gt;</font>
                        <font color="#008b8b">&lt;</font><font color="#008b8b">filter</font><font color="#008b8b">&gt;</font>
                                <font color="#008b8b">&lt;</font><font color="#008b8b">query</font><font color="#008b8b"> </font><font color="#2e8b57"><b>pattern</b></font>=<font color="#ff00ff">&quot;^\s*select\s+.*(table1(\s+|,).*table3(\s+|,|$))|(table3(\s+|,).*table1(\s+|,|$))&quot;</font><font color="#008b8b">/&gt;</font>
                        <font color="#008b8b">&lt;/filter&gt;</font>
                        <font color="#0000ff">&lt;!</font><font color="#0000ff">-- send any other queries to &quot;mysqldb&quot; --</font><font color="#0000ff">&gt;</font>
                        <font color="#008b8b">&lt;</font><font color="#008b8b">route</font><font color="#008b8b"> </font><font color="#2e8b57"><b>host</b></font>=<font color="#ff00ff">&quot;&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>port</b></font>=<font color="#ff00ff">&quot;&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>socket</b></font>=<font color="#ff00ff">&quot;/tmp/mysqldb.socket&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>user</b></font>=<font color="#ff00ff">&quot;mysqldbuser&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>password</b></font>=<font color="#ff00ff">&quot;mysqldbpassword&quot;</font><font color="#008b8b">&gt;</font>
                                <font color="#008b8b">&lt;</font><font color="#008b8b">query</font><font color="#008b8b"> </font><font color="#2e8b57"><b>pattern</b></font>=<font color="#ff00ff">&quot;.*&quot;</font><font color="#008b8b">/&gt;</font>
                        <font color="#008b8b">&lt;/route&gt;</font>
                <font color="#008b8b">&lt;/router&gt;</font>
        <font color="#008b8b">&lt;/instance&gt;</font>

<font color="#008b8b">&lt;/instances&gt;</font>
</pre>
</blockquote>

<span class="heading1">Filter Out Queries That Lack a Where Clause</span>

<p>Here's an example configuration which filters out queries which lack a where
clause.</span>

<blockquote>
<pre>
<font color="#0000ff">&lt;?</font><font color="#2e8b57"><b>xml</b></font><font color="#2e8b57"><b> </b></font><font color="#2e8b57"><b>version</b></font>=<font color="#ff00ff">&quot;1.0&quot;</font><font color="#0000ff">?&gt;</font>
<font color="#008b8b">&lt;!</font><font color="#a52a2a"><b>DOCTYPE</b></font> instances <font color="#a52a2a"><b>SYSTEM</b></font> <font color="#ff00ff">&quot;sqlrelay.dtd&quot;</font><font color="#008b8b">&gt;</font>

<font color="#008b8b">&lt;</font><font color="#008b8b">instances</font><font color="#008b8b">&gt;</font>

        <font color="#0000ff">&lt;!</font><font color="#0000ff">-- This instance maintains connections to a MySQL database --</font><font color="#0000ff">&gt;</font>
        <font color="#008b8b">&lt;</font><font color="#008b8b">instance</font><font color="#008b8b"> </font><font color="#2e8b57"><b>id</b></font>=<font color="#ff00ff">&quot;mysqldb&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>port</b></font>=<font color="#ff00ff">&quot;&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>socket</b></font>=<font color="#ff00ff">&quot;/tmp/mysqldb.socket&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>dbase</b></font>=<font color="#ff00ff">&quot;mysql&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>connections</b></font>=<font color="#ff00ff">&quot;3&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>maxconnections</b></font>=<font color="#ff00ff">&quot;15&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>maxqueuelength</b></font>=<font color="#ff00ff">&quot;5&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>growby</b></font>=<font color="#ff00ff">&quot;1&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>ttl</b></font>=<font color="#ff00ff">&quot;60&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>endofsession</b></font>=<font color="#ff00ff">&quot;commit&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>sessiontimeout</b></font>=<font color="#ff00ff">&quot;600&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>runasuser</b></font>=<font color="#ff00ff">&quot;nobody&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>runasgroup</b></font>=<font color="#ff00ff">&quot;nobody&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>cursors</b></font>=<font color="#ff00ff">&quot;5&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>authtier</b></font>=<font color="#ff00ff">&quot;listener&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>handoff</b></font>=<font color="#ff00ff">&quot;pass&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>deniedips</b></font>=<font color="#ff00ff">&quot;&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>allowedips</b></font>=<font color="#ff00ff">&quot;&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>debug</b></font>=<font color="#ff00ff">&quot;none&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>maxquerysize</b></font>=<font color="#ff00ff">&quot;65536&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>maxstringbindvaluelength</b></font>=<font color="#ff00ff">&quot;4000&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>maxlobbindvaluelength</b></font>=<font color="#ff00ff">&quot;71680&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>idleclienttimeout</b></font>=<font color="#ff00ff">&quot;-1&quot;</font><font color="#008b8b">&gt;</font>
                <font color="#008b8b">&lt;</font><font color="#008b8b">users</font><font color="#008b8b">&gt;</font>
                        <font color="#008b8b">&lt;</font><font color="#008b8b">user</font><font color="#008b8b"> </font><font color="#2e8b57"><b>user</b></font>=<font color="#ff00ff">&quot;mysqldbuser&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>password</b></font>=<font color="#ff00ff">&quot;mysqldbpassword&quot;</font><font color="#008b8b">/&gt;</font>
                <font color="#008b8b">&lt;/users&gt;</font>
                <font color="#008b8b">&lt;</font><font color="#008b8b">connections</font><font color="#008b8b">&gt;</font>
                        <font color="#008b8b">&lt;</font><font color="#008b8b">connection</font><font color="#008b8b"> </font><font color="#2e8b57"><b>connectionid</b></font>=<font color="#ff00ff">&quot;db&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>string</b></font>=<font color="#ff00ff">&quot;user=mysqldbuser;password=mysqldbpassword;host=mysqldb;db=mysqldb;&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>metric</b></font>=<font color="#ff00ff">&quot;1&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>behindloadbalancer</b></font>=<font color="#ff00ff">&quot;no&quot;</font><font color="#008b8b">/&gt;</font>
                <font color="#008b8b">&lt;/connections&gt;</font>
        <font color="#008b8b">&lt;/instance&gt;</font>


        <font color="#0000ff">&lt;!</font><font color="#0000ff">-- This instance sends queries to the</font>
<font color="#0000ff">                mysqldb instance after filtering them --</font><font color="#0000ff">&gt;</font>
        <font color="#008b8b">&lt;</font><font color="#008b8b">instance</font><font color="#008b8b"> </font><font color="#2e8b57"><b>id</b></font>=<font color="#ff00ff">&quot;router&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>port</b></font>=<font color="#ff00ff">&quot;9000&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>socket</b></font>=<font color="#ff00ff">&quot;/tmp/router.socket&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>dbase</b></font>=<font color="#ff00ff">&quot;router&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>connections</b></font>=<font color="#ff00ff">&quot;3&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>maxconnections</b></font>=<font color="#ff00ff">&quot;15&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>maxqueuelength</b></font>=<font color="#ff00ff">&quot;5&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>growby</b></font>=<font color="#ff00ff">&quot;1&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>ttl</b></font>=<font color="#ff00ff">&quot;60&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>endofsession</b></font>=<font color="#ff00ff">&quot;commit&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>sessiontimeout</b></font>=<font color="#ff00ff">&quot;600&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>runasuser</b></font>=<font color="#ff00ff">&quot;nobody&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>runasgroup</b></font>=<font color="#ff00ff">&quot;nobody&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>cursors</b></font>=<font color="#ff00ff">&quot;5&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>authtier</b></font>=<font color="#ff00ff">&quot;listener&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>handoff</b></font>=<font color="#ff00ff">&quot;pass&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>deniedips</b></font>=<font color="#ff00ff">&quot;&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>allowedips</b></font>=<font color="#ff00ff">&quot;&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>debug</b></font>=<font color="#ff00ff">&quot;none&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>maxquerysize</b></font>=<font color="#ff00ff">&quot;65536&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>maxstringbindvaluelength</b></font>=<font color="#ff00ff">&quot;4000&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>maxlobbindvaluelength</b></font>=<font color="#ff00ff">&quot;71680&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>idleclienttimeout</b></font>=<font color="#ff00ff">&quot;-1&quot;</font><font color="#008b8b">&gt;</font>
                <font color="#008b8b">&lt;</font><font color="#008b8b">users</font><font color="#008b8b">&gt;</font>
                        <font color="#008b8b">&lt;</font><font color="#008b8b">user</font><font color="#008b8b"> </font><font color="#2e8b57"><b>user</b></font>=<font color="#ff00ff">&quot;routeruser&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>password</b></font>=<font color="#ff00ff">&quot;routerpassword&quot;</font><font color="#008b8b">/&gt;</font>
                <font color="#008b8b">&lt;/users&gt;</font>
                <font color="#008b8b">&lt;</font><font color="#008b8b">router</font><font color="#008b8b">&gt;</font>
                        <font color="#0000ff">&lt;!</font><font color="#0000ff">-- send queries to &quot;mysqldb&quot; which match the</font>
<font color="#0000ff">                                specified patterns --</font><font color="#0000ff">&gt;</font>
                        <font color="#008b8b">&lt;</font><font color="#008b8b">route</font><font color="#008b8b"> </font><font color="#2e8b57"><b>host</b></font>=<font color="#ff00ff">&quot;&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>port</b></font>=<font color="#ff00ff">&quot;&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>socket</b></font>=<font color="#ff00ff">&quot;/tmp/mysqldb.socket&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>user</b></font>=<font color="#ff00ff">&quot;mysqldbuser&quot;</font><font color="#008b8b"> </font><font color="#2e8b57"><b>password</b></font>=<font color="#ff00ff">&quot;mysqldbpassword&quot;</font><font color="#008b8b">&gt;</font>
                                <font color="#0000ff">&lt;!</font><font color="#0000ff">-- allow creates --</font><font color="#0000ff">&gt;</font>
                                <font color="#008b8b">&lt;</font><font color="#008b8b">query</font><font color="#008b8b"> </font><font color="#2e8b57"><b>pattern</b></font>=<font color="#ff00ff">&quot;^\s*create\s+table\s+&quot;</font><font color="#008b8b">/&gt;</font>
                                <font color="#0000ff">&lt;!</font><font color="#0000ff">-- allow drops --</font><font color="#0000ff">&gt;</font>
                                <font color="#008b8b">&lt;</font><font color="#008b8b">query</font><font color="#008b8b"> </font><font color="#2e8b57"><b>pattern</b></font>=<font color="#ff00ff">&quot;^\s*drop\s+table\s+&quot;</font><font color="#008b8b">/&gt;</font>
                                <font color="#0000ff">&lt;!</font><font color="#0000ff">-- allow inserts --</font><font color="#0000ff">&gt;</font>
                                <font color="#008b8b">&lt;</font><font color="#008b8b">query</font><font color="#008b8b"> </font><font color="#2e8b57"><b>pattern</b></font>=<font color="#ff00ff">&quot;^\s*insert\s+into\s+&quot;</font><font color="#008b8b">/&gt;</font>
                                <font color="#0000ff">&lt;!</font><font color="#0000ff">-- allow deletes --</font><font color="#0000ff">&gt;</font>
                                <font color="#008b8b">&lt;</font><font color="#008b8b">query</font><font color="#008b8b"> </font><font color="#2e8b57"><b>pattern</b></font>=<font color="#ff00ff">&quot;^\s*delete\s+from\s+&quot;</font><font color="#008b8b">/&gt;</font>
                                <font color="#0000ff">&lt;!</font><font color="#0000ff">-- allow updates --</font><font color="#0000ff">&gt;</font>
                                <font color="#008b8b">&lt;</font><font color="#008b8b">query</font><font color="#008b8b"> </font><font color="#2e8b57"><b>pattern</b></font>=<font color="#ff00ff">&quot;^\s*update\s+&quot;</font><font color="#008b8b">/&gt;</font>
                                <font color="#0000ff">&lt;!</font><font color="#0000ff">-- allow selects with a where clause --</font><font color="#0000ff">&gt;</font>
                                <font color="#008b8b">&lt;</font><font color="#008b8b">query</font><font color="#008b8b"> </font><font color="#2e8b57"><b>pattern</b></font>=<font color="#ff00ff">&quot;^\s*select\s+.*\s+from\s+.*\s+where\s+&quot;</font><font color="#008b8b">/&gt;</font>
                        <font color="#008b8b">&lt;/route&gt;</font>
                        <font color="#0000ff">&lt;!</font><font color="#0000ff">-- all other queries will be filtered out --</font><font color="#0000ff">&gt;</font>
                        <font color="#008b8b">&lt;</font><font color="#008b8b">filter</font><font color="#008b8b">&gt;</font>
                                <font color="#008b8b">&lt;</font><font color="#008b8b">query</font><font color="#008b8b"> </font><font color="#2e8b57"><b>pattern</b></font>=<font color="#ff00ff">&quot;.*&quot;</font><font color="#008b8b">/&gt;</font>
                        <font color="#008b8b">&lt;/filter&gt;</font>
                <font color="#008b8b">&lt;/router&gt;</font>
        <font color="#008b8b">&lt;/instance&gt;</font>

<font color="#008b8b">&lt;/instances&gt;</font>
</pre>
</blockquote>

<span class="heading1">Quirks and Limitations</span>

<p>Behind the scenes, the router maintains parallel transactions on each of the
databases that it is routing queries to.  When the client issues a begin, commit
or rollback, the router issues a begin, commit or rollback to each of the
databases.  Similarly, if the client turns auto-commit on or off, the router
turns auto-commit on or off on each of the databases.</p>

<p>There are scenarios where a commit, rollback or auto-commit on/off command
could succeed on some of the databases and fail on others.  Some databases
have a 2-phase commit feature to handle these scenarios.  With 2-phase commit,
you can roll back a commit until you do second commit.  Many databases don't
support 2-phase commit though.  At present, SQL Relay doesn't currently support
2-phase commit for any databases.  So, currently, SQL Relay doesn't handle these
scenarios very well.  It returns false, indicating than the operation failed,
but there's no good way to know which databases succeeded and which failed.
Ideally, SQL Relay would remove the database that failed, stop sending queries
to it, notify someone that it has gotten out of sync and log all of the queries
that would have gone to it so they can be replayed later, or something like
that.  But it doesn't currently do any of that.</p>

<p>Since queries may be routed to different kinds of databases, the router has
to employ some tricks to maintain parallel transactions.  Some databases run
in auto-commit mode by default and must be issued a "begin" query to start a
transaction.  Other databases implicitly start a new transaction when a client
logs in and after each commit or rollback.  If any of the databases being
routed to require a "begin" query to start a transaction, then the ones that
don't are put in auto-commit mode when the client logs in and after each commit
or rollback and are taken out of auto-commit mode when the client sends a begin
query.  If none of the databases being routed to require a "begin" query to
start a transaction, then the databases are not put in auto-commit mode when
the client logs in or after each commit or rollback.  Rather, transactions are
implicitly started by the database.  For example, if your client application
is using a router which routes quries over both PostgreSQL and Oracle databases,
then since PostgreSQL requires "begin" queries, you must use a "begin" query to
start a transaction, even if your app only intends to send queries which would
be run against Oracle.  Conversely, if your client application is using a router
which only routes queries over a set of Oracle databases, then you do not have
to use "begin" quries.</p>

<p>Since each query is only sent to a single database it's not possible to
join tables residing on different databases.  However, a limited version of
this feature is planned.</p>

<p>SQL Relay's query routing feature does not yet support sending a single
query to multiple databases and merging the result sets.  But this feature is
planned.</p>

<p>It's possible to use stored procedures with SQL Relay's query routing
feature.  However, since stored procedures are run on the database, SQL Relay
can't route the individual queries run inside the stored procedure.  So, the
stored procedure and all queries run inside of it will be run against whichever
database it was routed to.</p>

</body>
</html>
